//STEP 2: load the individual-level data, clean, merge with Government spending data and controls

insheet 'PATH/tu_full_govspend.csv'

ge year=substr(date,1,4)
drop date
destring year, replace
keep if year==2007 | year==2008| year==2010
egen new_id = group(cid)

keep new_id year state county zipcode cbsa cards_bal total_bal mtg_bal auto_bal inc_tu vantage_score 
rename vantage_score score
ge fips = state * 1000 + county
drop state county


replace cards_bal=0 if cards_bal ==. | cards_bal < 0
replace total_bal=0 if total_bal ==. | total_bal < 0
replace auto_bal =0 if auto_bal  ==. | auto_bal < 0
replace mtg_bal  =0 if mtg_bal   ==. | mtg_bal < 0
replace inc_tu   = inc_tu *1000   /* income is coded in thousand dollars */

replace inc_tu = . if inc_tu < 0   /* in TransUnion data, negative values are special codes for missing */

reshape wide dti fips zipcode cbsa cards_bal total_bal mtg_bal auto_bal inc_tu score , i(new_id) j(year)

rename cbsa2007 cbsa

* merging individual-level TU data with CBSA-level Government Spending and Obligations

sort cbsa
merge m:1 cbsa using DgSp_inc, force
keep if _merge!=2
drop _merge



*interactions of total debt in 0607 with GSpending and Gpredict

* ind level: total debt  total income in 2007
ge d2i07 = total_bal2007 / inc_tu2007    /* Data as of February each year, so in February 2007 we use it as dti in 2006 */

// interaction with DSpending
ge Gsp_d2i07 = DgSp_inc * d2i07

/* demean dti before interacting with GSpending */
egen tm_d2i07= mean(d2i07), by(cbsa)
ge d2i07_dm = d2i07-tm_d2i07
drop tm_d2i07
generate Gsp_d2i07_dm = DgSp_inc * d2i07_dm

// if there is no data for spending, zero spending
ge      Gsp_d2i07_dm_nm  = 0         if Gsp_d2i07_dm==.
replace Gsp_d2i07_dm_nm  = Gsp_d2i07_dm if Gsp_d2i07_dm!=.


* obligations
generate Gsp_d2i07_ob_dm = DgOb_inc * d2i07_dm
// if there is no data for obligations, zero obligations
ge      Gsp_d2i07_ob_dm_nm = Gsp_d2i07_ob_dm if DgOb_inc !=.
replace Gsp_d2i07_ob_dm_nm = 0 if DgOb_inc==.


  
* create instruments

ge DpredictNS_spendInc_nm = 0 if DpredictNS_spendInc==.
replace DpredictNS_spendInc_nm = DpredictNS_spendInc if DpredictNS_spendInc !=.

ge DpredictNS_ObInc_nm = 0 if DpredictNS_ObInc==.
replace DpredictNS_ObInc_nm = DpredictNS_ObInc if DpredictNS_ObInc !=.

ge DpredictNS_spendInc_dm_nm_dti07 = DpredictNS_spendInc_nm * d2i07_dm
ge DpredictNS_ObInc_dm_nm_dti07 = DpredictNS_ObInc_nm * d2i07_dm

replace score2007=. if score2007<0
rename score2007 score


ge      subprime = 1 if score >=501 & score<=700
replace subprime = 0 if subprime==. 

ge      nearprime = 1 if score >=701 & score<=800
replace nearprime = 0 if nearprime==. 

ge      prime = 1 if score >=801 & score<=900
replace prime = 0 if prime==.

ge      superprime = 1 if score >=901 & score<=990
replace superprime = 0 if superprime==.

foreach var in  subprime nearprime prime superprime {

ge DgSp_`var' = DgSp_inc * `var'
ge d2i_`var' = d2i07 * `var'
ge d2i_Gsp`var' = d2i07 * DgSp_inc* `var'

ge DgOb_`var' = DgOb_inc * `var'
ge d2i_obl`var' = d2i07 * DgOb_inc* `var'
ge d2i_dm_obl`var'_dm = d2i07_dm * DgOb_inc* `var'_dm
ge d2i_dm_obl`var' = d2i07_dm * DgOb_inc* `var'

// demean prior interacting for robustness 
egen tm_`var'= mean(`var'), by(cbsa)
ge `var'_dm = `var'-tm_`var'
drop tm_`var'
ge d2i_`var'_dm = d2i07_dm * `var'_dm
ge d2i_dm_`var' = d2i07_dm * `var'
ge d2i_dm_Gsp`var'_dm = d2i07_dm * DgSp_inc* `var'_dm
ge d2i_dm_Gsp`var' = d2i07_dm * DgSp_inc* `var'

ge d2i_dm_obl`var'_dm_iv = d2i07_dm * DpredictNS_ObInc_nm * `var'_dm
ge d2i_dm_spend`var'_dm_iv = d2i07_dm * DpredictNS_spendInc_nm * `var'_dm

}
                                                                       
ge log_inctu07 = log(inc_tu2007)



/*
// data from Mian and Sufi (Econometrica) 
use "PATH/miansufieconometrica_countylevel.dta", clear
keep fips white medhhinc owner educ_lths educ_hs unemp pov urban C2D06share*
sort fips
save MandS_cty_controls06, replace

use "PATH/IndEmpShare06.dta", clear
forvalues i = 1(1) 20 {
rename empshare`i' cty_empshare`i'
} 
sort fips
keep fips cty_empshare*
save ctyIndEmpShare06, replace
*/

sort fips
merge m:1 fips using MandS_cty_controls06
drop if _merge==2
drop _merge
sort fips
merge m:1 fips using ctyIndEmpShare06
drop if _merge==2
drop _merge


drop d_qrt_d2i_cty1-Gsp_d2i_cty5

replace d2i07 =. if d2i07 >= 5.8  /* drop above 99th percentile. likely erroneous */

ge d_cards1008 =( cards_bal2010 - cards_bal2008)/inc_tu2008
ge d_total1008 =( total_bal2010 - total_bal2008)/inc_tu2008
ge d_mtges1008 = (mtg_bal2010 - mtg_bal2008) /inc_tu2008 

winsor d_cards1008 , gen(wd_cards1008) p(0.01)
winsor d_total1008 , gen(wd_total1008) p(0.01)
winsor d_mtges1008 , gen(wd_mtges1008) p(0.01)


save tu_individual_gspend, replace

/////////////////////////////////////////////////////

// Table 4: cards, total, mortgages (columns 1-3)

set more off, perm
* cards 
reghdfe wd_cards1008 d2i07  log_inctu07 cty_empshare1-cty_empshare19 white medhhinc owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_dm_nm = DpredictNS_spendInc_dm_nm_dti07) , absorb(cbsa) vce(cluster cbsa)
reghdfe wd_cards1008 d2i07 log_inctu07 cty_empshare1-cty_empshare19 white medhhinc owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_ob_dm_nm = DpredictNS_ObInc_dm_nm_dti07) , absorb(cbsa) vce(cluster cbsa)

* total debt 
reghdfe wd_total1008 d2i07  log_inctu07 cty_empshare1-cty_empshare19 white  owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_dm_nm = DpredictNS_spendInc_dm_nm_dti07), absorb(cbsa ) vce(cluster cbsa)
reghdfe wd_total1008 d2i07  log_inctu07 cty_empshare1-cty_empshare19 white  owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_ob_dm_nm = DpredictNS_ObInc_dm_nm_dti07), absorb(cbsa ) vce(cluster cbsa)

* mortgages
reghdfe wd_mtges1008 d2i07 log_inctu07 cty_empshare1-cty_empshare19 white medhhinc owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_dm_nm = DpredictNS_spendInc_dm_nm_dti07), absorb(cbsa) vce(cluster cbsa)
reghdfe wd_mtges1008 d2i07 log_inctu07 cty_empshare1-cty_empshare19 white medhhinc owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_ob_dm_nm = DpredictNS_ObInc_dm_nm_dti07), absorb(cbsa) vce(cluster cbsa)

// auto loans: Table 4, Column 4
use PATH/tu_individual_gspend, clear
keep if auto_bal2010 >0 | auto_bal2008 >0
winsor auto_bal2010 , gen(wauto_bal2010) p(0.01)
winsor auto_bal2008 , gen(wauto_bal2008) p(0.01)
ge d_auto_bal1008 = (wauto_bal2010 - wauto_bal2008) / inc_tu2008
winsor d_auto_bal1008 , gen(wd_auto_bal1008) p(0.01)

generate Gsp_d2i07 = DgSp_inc * d2i07

reghdfe wd_auto_bal1008 d2i07 log_inctu07 cty_empshare1-cty_empshare19 white medhhinc owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_ob_dm_nm = DpredictNS_ObInc_dm_nm_dti07), absorb(cbsa) vce(cluster cbsa)
est store autoloans_ob
reghdfe wd_auto_bal1008 d2i07 log_inctu07 cty_empshare1-cty_empshare19 white medhhinc owner educ_lths educ_hs unemp pov urban (Gsp_d2i07_dm_nm = DpredictNS_spendInc_dm_nm_dti07), absorb(cbsa) vce(cluster cbsa)


// Table 5. 
//  ZIP CODE LEVEL ANALYSIS.

set more off
clear all

* auto registrations
use Polk0709, clear
drop zipstring
sort zipcode
rename cye_2007 n_auto07
rename cye_2009 n_auto09
save new_auto_registrations_07_09, replace


use PATH/tu_full_govspend, clear
ge year=substr(date,1,4)
drop date
destring year, replace
keep if year==2007
keep total_bal zipcode inc_tu vantage_score

replace total_bal = . if total_bal<0
replace inc_tu = . if inc_tu<0

collapse (sum) total_bal inc_tu (mean) av_inc = inc_tu av_score = vantage_score av_dti = dti (count) num_cid = total_bal , by(zipcode)

ge dti = total_bal/(inc_tu*1000)

drop if dti>4  /* outlier */

sort zipcode

merge 1:1 zipcode using PATH/zip_to_county_to_cbsa_2000.dta
keep if _merge !=2

drop _merge

rename county fips
sort fips
merge m:1 fips using MandS_cty_controls06
drop if _merge==2
drop _merge
sort fips
merge m:1 fips using ctyIndEmpShare06
drop if _merge==2
drop _merge


sort cbsa
merge m:1 cbsa using DgSp_inc
drop _merge

generate DgSp_inc1_dti = DgSp_inc * dti

rename dti dti_zip


/* use demeaned dti in 2006 on the CBSA level */

egen tm_dti_zip = mean(dti_zip), by(cbsa)
ge dti_zip_dm = dti_zip-tm_dti_zip
drop tm_dti_zip

ge DgSp_inc1_dti_zip_dm = dti_zip_dm * DgSp_inc

ge      DgSp_inc1_dti_zip_nm = DgSp_inc1_dti_zip if DgSp_inc!=.
replace DgSp_inc1_dti_zip_nm = 0 if DgSp_inc==.

ge      DgSp_inc1_dti_zip_dm_nm = DgSp_inc1_dti_zip_dm if DgSp_inc!=.
replace DgSp_inc1_dti_zip_dm_nm = 0 if DgSp_inc==.

sort zipcode 
duplicates report zipcode
duplicates drop zipcode, force 

merge 1:1 zipcode using new_auto_registrations_07_09
keep if _merge==3
drop _merge

ge auto_gr_zip_1 = log(1+n_auto09) - log(1+n_auto07)

ge log_av_inc = log(av_inc)
ge log_av_score = log(av_score)

ge DgSp_obl_dti_zip_dm = dti_zip_dm * DgOb_inc
ge DgSp_obl_dti_zip = dti_zip * DgOb_inc

ge      DgSp_obl_dti_zip_nm = DgSp_obl_dti_zip if DgOb_inc!=.
replace DgSp_obl_dti_zip_nm = 0 if DgOb_inc==.

ge      DgSp_obl_dti_zip_dm_nm = DgSp_obl_dti_zip_dm if DgOb_inc!=.
replace DgSp_obl_dti_zip_dm_nm = 0 if DgOb_inc==.

* instruments

ge DpredictNS_ObInc_nm = DpredictNS_ObInc if DgSp_inc !=.
replace DpredictNS_ObInc_nm = 0 if DgOb_inc ==.
generate DgOb_inc_dti06_iv = DpredictNS_ObInc_nm * dti_zip_dm

ge DpredictNS_spendInc_nm = DpredictNS_spendInc if DgSp_inc !=.
replace DpredictNS_spendInc_nm = 0 if DgSp_inc ==.
generate DgSp_inc_dti06_iv = DpredictNS_spendInc_nm * dti_zip_dm

generate DgSp_inc_dti06_iv_mis = DpredictNS_spendInc * dti_zip_dm


* REGRESSIONS
* spending
reghdfe auto_gr_zip_1  dti_zip  log_av_score log_av_inc cty_empshare1-cty_empshare19  white medhhinc owner educ_lths educ_hs unemp pov urban (DgSp_inc1_dti_zip_dm_nm=DgSp_inc_dti06_iv),  absorb (cbsa) vce(cluster cbsa) 
* obligations
reghdfe auto_gr_zip_1  dti_zip  log_av_score log_av_inc cty_empshare1-cty_empshare19  white medhhinc owner educ_lths educ_hs unemp pov urban (DgSp_obl_dti_zip_dm_nm=DgOb_inc_dti06_iv),  absorb (cbsa) vce(cluster cbsa) 










